/*
THIS FILE CONVERTS THE TOTAL RETURN INDEX, EARNING-PRICE AND DIVIDEND-PRICE DOWNLOADED FROM DATASTREAM IN A .DTA FORMAT.
IN THE EXCEL FILE OBTAINED FROM DATASTREAM, EACH SHEET CORRESPONDS TO A COUNTRY. IMPORT EACH SHEET SEPARETELY AND MERGE THEM.
*/
clear all
global countries "  australia    austria    belgium    canada    brazil    chile    china    colombia    czechrepublic    denmark    egypt    france    finland    germany    greece    hongkong    hungary    india    indonesia    ireland    israel    italy    japan    netherlands    newzealand    panama    kuwait    norway    portugal    singapore    spain    sweden    switzerland    argentina    bahrain    bangladesh    bosniaherzegovina    botswana    bulgaria  cyprus  croatia    ecuador    estonia    ghana    iceland    ivorycoast    jamaica    kazakhstan    kenya    latvia    jordan    lebanon    lithuania    mauritius    morocco    namibia    nigeria    oman    pakistan    romania    saudiarabia    serbia    slovakia    slovenia    srilanka    trinidadandtobago    tunisia    ukraine    vietnam    zambia    zimbabwe    unitedkingdom    koreasouth    malaysia    mexico    peru    philippines    poland    russianfederation    qatar    southafrica    taiwan    thailand    turkey    unitedarabemirates    unitedstates            "
global nonUScountries "  australia    austria    belgium    canada    brazil    chile    china    colombia    czechrepublic    denmark    egypt    france    finland    germany    greece    hongkong    hungary    india    indonesia    ireland    israel    italy    japan    netherlands    newzealand    panama    kuwait    norway    portugal    singapore    spain    sweden    switzerland    argentina    bahrain    bangladesh    bosniaherzegovina    botswana    bulgaria cyprus   croatia    ecuador    estonia    ghana    iceland    ivorycoast    jamaica    kazakhstan    kenya    latvia    jordan    lebanon    lithuania    mauritius    morocco    namibia    nigeria    oman    pakistan    romania    saudiarabia    serbia    slovakia    slovenia    srilanka    trinidadandtobago    tunisia    ukraine    vietnam    zambia    zimbabwe    unitedkingdom    koreasouth    malaysia    mexico    peru    philippines    poland    russianfederation    qatar    southafrica    taiwan    thailand    turkey    unitedarabemirates               "

*****************************************************************************
* Import excel files containing the MSCI variables, save it locally, merge them together and reshape them in a nice panel form
foreach sheet of global countries{

	display "`sheet'"
	tempfile tempeq_`sheet' // save each data locally
	qui import excel "$path/datastream/equity_indices_clean.xlsx", clear sheet("`sheet'") cellrange(A6) firstrow 
	* replace NAs to destring the variables later
	ds, has(type string) 
	qui foreach v in `r(varlist)' { 
		replace `v' = "" if trim(`v') == "NA" 
	} 
	qui destring _all, replace force
	* gen date
	qui gen datem = mofd(date)
	format datem %tm
	qui drop date
	qui drop if missing(datem)
	
	qui save "`tempeq_`sheet''", replace
}
*****************************************************************************

*****************************************************************************
* merge
use "`tempeq_unitedstates'", clear
foreach sheet of global nonUScountries {
		merge 1:1 datem using "`tempeq_`sheet''", nogenerate
}
// Delete each variable without any observation for all countries
foreach var of varlist _all {
	capture assert mi(`var')
	 if !_rc {
		drop `var'
	 }
}
*****************************************************************************

*****************************************************************************
* reshape
qui reshape long msci_ usdmsci_ usdreturn_ usddy_ usdpe_ return_  dy_ pe_ mvalue_ , i(datem) j(country) string
/*
- msci: equity price index in local currency
- usdmsci: equity price index in USD
- return: total return index in local currency
- usdreturn: total return index in USD
- pe = usdpe: price-earning ratio in USD
- dy = usddy: dividend-price ratio in USD
- mvalue: market value in USD
*/

* rename
foreach var in "usdmsci" "usdreturn" "usddy" "usdpe" "msci" "return" "dy" "pe" "mvalue" { 
	qui rename `var'_ `var'
}
*****************************************************************************

*****************************************************************************
*** FINAL data manipulation
* for the US: replace values in USD by local values (which are in USD but recorder as local by MSCI) 
replace usddy = dy if missing(usddy)
replace usdpe = pe if missing(usdpe)
drop dy pe
rename usddy dy
rename usdpe pe
gen ep = 1 / pe // earning-price instead of price-earning
*****************************************************************************

* save
save "$datapath/MSCI_exceldata_to_DTA.dta", replace
* end
*****************************************************************************